package com.yincheng.oes.business.util;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.javatuples.Pair;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

public class ExcelUtils {
    private final static String excel2003L = ".xls"; // 2003- 版本的excel
    private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

    /**
     * 只读取第一个sheet页内容,包括图片数据
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Pair<List<Map<String, XSSFPictureData>>, List<List<Object>>> getBankListByExcel(InputStream in,
            String fileName) throws Exception {
        List<List<Object>> list;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 读取Excel中第一个sheet页内容
        sheet = work.getSheetAt(0);
        if (sheet == null) {
            return null;
        }
        List<Map<String, XSSFPictureData>> pictureMaps = getPictureMaps(sheet);
        // 遍历当前sheet中的所有行
        for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j) {
                continue;
            }

            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                cell = row.getCell(y);
                li.add(getCellValue(cell));
            }
            list.add(li);
        }
        return Pair.with(pictureMaps, list);
    }

    /**
     * 获取excel文件中的数据集合,只去第一个sheet页
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
        List<List<Object>> list = null;

        // 创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;

        list = new ArrayList<List<Object>>();
        // 读取Excel中第一个sheet页内容
        sheet = work.getSheetAt(0);
        if (sheet == null) {
            return null;
        }
        // 遍历当前sheet中的所有行
        int cellNum = sheet.getRow(0).getLastCellNum();// 设定每行数据个数
        for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
            row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j || row.getCell(0) == null) {
                continue;
            }
            // 遍历所有的列
            List<Object> li = new ArrayList<Object>();
            for (int y = 0; y < cellNum; y++) {
                cell = row.getCell(y);
                if (cell == null)
                    li.add(null);
                else
                    li.add(getCellValue(cell));
            }
            list.add(li);
        }
        return list;
    }

    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     *
     * @param inStr,fileName
     * @return
     * @throws Exception
     */
    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(inStr); // 2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(inStr); // 2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }

    /**
     * 描述：对表格中数值进行格式化
     *
     * @param cell
     * @return
     */
    public static Object getCellValue(Cell cell) {
        Object value = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化

        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getRichStringCellValue().getString();
                break;
            case NUMERIC:
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = getRealStringValueOfDouble(cell.getNumericCellValue());
                } else if (cell.getCellStyle().getDataFormat() == 14) {
                    sdf = new SimpleDateFormat("yyyy/MM/dd");
                    Date date = cell.getDateCellValue();
                    value = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 21) {
                    sdf = new SimpleDateFormat("HH:mm:ss");
                    Date date = cell.getDateCellValue();
                    value = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 22 || cell.getCellStyle().getDataFormat() == 176) {
                    sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    Date date = cell.getDateCellValue();
                    value = sdf.format(date);
                } else {
                    throw new RuntimeException("日期格式错误!!!");
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue();
                break;
            case BLANK:
                value = null;
                break;
            default:
                break;
        }
        return value;
    }

    private static String getRealStringValueOfDouble(Double d) {
        String doubleStr = d.toString();
        boolean b = doubleStr.contains("E");
        int indexOfPoint = doubleStr.indexOf('.');
        if (b) {
            int indexOfE = doubleStr.indexOf('E');
            BigInteger xs = new BigInteger(doubleStr.substring(indexOfPoint + BigInteger.ONE.intValue(), indexOfE));
            int pow = Integer.valueOf(doubleStr.substring(indexOfE + BigInteger.ONE.intValue()));
            int xsLen = xs.toByteArray().length;
            int scale = xsLen - pow > 0 ? xsLen - pow : 0;
            doubleStr = String.format("%." + scale + "f", d);
        } else {
            java.util.regex.Pattern p = Pattern.compile(".0$");
            java.util.regex.Matcher m = p.matcher(doubleStr);
            if (m.find()) {
                doubleStr = doubleStr.replace(".0", "");
            }
        }
        return doubleStr;
    }

    public static List<Map<String, XSSFPictureData>> getPictureMaps(Sheet sheet) throws IOException {
        List<Map<String, XSSFPictureData>> pics = new ArrayList<>();
        List<POIXMLDocumentPart> list = ((POIXMLDocumentPart) sheet).getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();
                    String key = marker.getRow() + "-" + marker.getCol();
                    map.put(key, picture.getPictureData());
                    pics.add(map);
                }
            }
        }
        return pics;
    }

}
